package com.rdc.example.dao.impl;

import com.rdc.example.dao.SongDao;
import com.rdc.example.entity.Song;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

import static com.rdc.example.util.DBUtil.closeResources;
import static com.rdc.example.util.DBUtil.getConnection;

public class SongDaoImpl implements SongDao {

    @Override
    public boolean insert(Song song) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {// 获取连接
            conn = getConnection();
            String sql = "insert into `song` (user_id, column_id, check_id, likes, play_count, status, name, song_path, reason) values (?,?,?,?,?,?,?,?,?);";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            setSong(pstmt, song);
            // 以影响行数 row 判断并返回插入结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean deleteSongById(Integer id) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            String sql = "delete from `song` where id = ?;";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, id);
            // 以影响行数 row 判断并返回删除结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }
    @Override
    public boolean updateSong(Song song) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            String sql = "update `song` set user_id = ?, column_id = ?,check_id = ?, likes = ?, play_count = ?, status = ?,name = ?, song_path = ?, reason = ? where id=?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            setSong(pstmt, song);
            pstmt.setInt(10, song.getId());
            // 以影响行数 row 判断并返回删除结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public Song getSongById(Integer id) throws Exception {
        // 进行数据库查询操作
        Connection conn =getConnection();
        PreparedStatement pstmt=null;
        ResultSet rs = null;
        Song song = null;
        try{
            String sql = "select * from `song` where id=?";
            //执行sql语句
            pstmt = conn.prepareStatement(sql);
            //设值
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            //封装user数据
            if (rs.next()) {
                song = getSong(rs);
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(rs,pstmt,conn);
        }
        return song;
    }

    @Override
    public Song getRandomSong() throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Song song = null;
        try {
            conn = getConnection();
            //随机查询SQL
            //最大id*（0-1）随机数，找大于等于这个数的id
            String sql ="select * from song where id >= (select floor(rand() * (select max(id) from song where status = 2))) and status = 2 order by id limit 1;";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                song = getSong(rs);
            }
        } catch (SQLException e) {
            throw new RuntimeException("随机歌曲查询失败", e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return song;
    }

    @Override
    public boolean addLike(Integer id) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        try {
            String sql = "update `song` set likes = likes + 1 where id = ?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, id);
            // 以影响行数 row 判断并返回结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean reduceLike(Integer id) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            String sql = "update `song` set likes = likes - 1 where id = ?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, id);
            // 以影响行数 row 判断并返回结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean addPlayCount(Integer id) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        try {
            String sql = "update `song` set play_count = play_count + 1 where id = ?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, id);
            // 以影响行数 row 判断并返回结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean addColumn(Integer id, Integer columnId) throws Exception {
        // 进行数据库更新操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update `song` set column_id=? where id=?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            pstmt.setInt(2, id);
            int row = pstmt.executeUpdate();
            // 以影响行数 row 判断并返回结果
            return row > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean removeColumn(Integer id) throws Exception {
        // 进行数据库更新操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update `song` set column_id = null where id = ?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            int row = pstmt.executeUpdate();
            // 以影响行数 row 判断并返回结果
            return row > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean removeColumnAll(Integer columnId) throws Exception {
        // 进行数据库更新操作
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = getConnection();
            String sql = "update `song` set column_id = null where column_id = ?";
            // 执行 sql 语句
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            int row = pstmt.executeUpdate();
            // 以影响行数 row 判断并返回结果
            return row > 0;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            closeResources(null, pstmt, conn);
        }
    }





    @Override
    public List<Song> listSongByUserId(Integer userId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Song> songList = new ArrayList<>();
        try {
            String sql = "select * from `song` where user_id=? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Song song = getSong(rs);
                songList.add(song);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public List<Song> getSongListByPageAndUserId(Integer userId, Integer pageBegin, Integer pageSize) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Song> songList = new ArrayList<>();
        try {
            String sql = "select * from `song` where user_id=?  limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Song song = getSong(rs);
                songList.add(song);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public Integer countSongByUserId(Integer userId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from `song` where user_id = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }

    @Override
    public List<Song> getSongListByPageAndColumnId(Integer columnId, Integer pageBegin, Integer pageSize) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Song> songList = new ArrayList<>();
        try {//要过审的
            String sql = "select * from `song` where (column_id=? and status = 2) limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Song song = getSong(rs);
                songList.add(song);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public Integer countSongByColumnId(Integer columnId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {//要过审的
            String sql = "select count(*) from `song` where (column_id = ? and status = 2)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }

    @Override
    public List<Song> getSongListByPageAndColumnIdWithNull(Integer columnId, Integer userId,Integer pageBegin, Integer pageSize) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Song> songList = new ArrayList<>();
        try {//获取指定userId的对应columnId或者是为columnId为null的音乐,并且进行排序
            //要过审的
            String sql = "select * from `song` where (column_id is  null or column_id = ?) and user_id = ? and status = 2 order by (column_id = ?) desc LIMIT ?, ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            pstmt.setInt(2, userId);
            pstmt.setInt(3, columnId);
            pstmt.setInt(4, pageBegin);
            pstmt.setInt(5, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Song song = getSong(rs);
                songList.add(song);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public Integer countSongByColumnIdWithNull(Integer columnId , Integer userId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {//要过审的
            String sql = "select count(*) from `song` where (column_id is null or column_id = ?) and user_id = ? and status =2";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, columnId);
            pstmt.setInt(2, userId);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }

    @Override
    public List<Song> searchSongsByTitleAndLikeDesc(String keyword, Integer pageBegin, Integer pageSize) throws Exception {
        List<Song> songList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {//要过审的
            conn = getConnection();
            String sql = "select * from `song` where name like ? and  status =2 order by likes desc limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                songList.add(getSong(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public List<Song> searchSongsByTitleAndCreatAtDesc(String keyword, Integer pageBegin, Integer pageSize) throws Exception {
        List<Song> songList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {//要过审的
            conn = getConnection();
            String sql = "select * from `song` where name like ? and status = 2 order by created_at desc limit ?,?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                songList.add(getSong(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public Integer countSearchSongsByTitle(String keyword) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {//两个 歌曲搜索排序的方法公用这个方法
            //要过审的
            String sql = "select count(*) from `song` where name like ? and status =2";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }

    @Override
    public List<Song> getSongListForReview(Integer checkId, Integer pageBegin, Integer pageSize) throws Exception {
        List<Song> songList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            //查询所有'我'审核中的歌曲和未审核歌曲 ,审核中排前面
            String sql = "select * from song where status in (0, 1) and (check_id = ? or check_id is null)  order by check_id is not null desc, created_at asc limit ?, ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, checkId);
            pstmt.setInt(2, pageBegin);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                songList.add(getSong(rs));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeResources(rs, pstmt, conn);
        }
        return songList;
    }

    @Override
    public Integer countSongListForReview(Integer checkId) throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            String sql = "select count(*) from `song` where status in (0, 1) and (check_id = ? or check_id is null)  ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, checkId );
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return 0;
    }


    @Override
    public boolean startReview(Integer songId, Integer checkId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            /*  -1不通过 0待审核 1审核中 2通过  默认0
            * 如果一个歌曲被管理员a点击，触发startReview方法,返回ture。check_id 被设值
            * 这时管理员b/c/d/e...也点了，触发startReview方法，因为check_id is null不满足，row为0 返回false
            * */
            String sql = "update `song` set status = 1, check_id= ? where id = ? and check_id is null and status = 0";
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1,checkId);
            pstmt.setInt(2, songId);
            // 以影响行数 row 判断并返回删除结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean passReview(Integer songId) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            //1不通过 0待审核 1审核中 2通过  默认0
            String sql = "update `song` set status = 2 where id=? ";
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setInt(1, songId);
            // 以影响行数 row 判断并返回删除结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }

    @Override
    public boolean rejectReview(Integer songId, String reason) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // 获取连接
            conn = getConnection();
            //1不通过 0待审核 1审核中 2通过  默认0
            String sql = "update `song` set status = -1 ,reason = ? where id=? ";
            pstmt = conn.prepareStatement(sql);
            // 设值
            pstmt.setString(1, reason);
            pstmt.setInt(2, songId);
            // 以影响行数 row 判断并返回删除结果
            int row = pstmt.executeUpdate();
            return row > 0;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(null, pstmt, conn);
        }
    }



    @Override
    public List<Song> getSongListByIdList(List<Integer> ids, Integer pageBegin, Integer pageSize) throws Exception {
        List<Song> songList = new ArrayList<>();
        if (ids == null || ids.isEmpty()) {
            return songList;
        }
        String sql = "select * from `song` where id in (" +
                ids.stream()
                        .filter(Objects::nonNull)
                        .map(String::valueOf)
                        .collect(Collectors.joining(",")) + ")limit ?,?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, pageBegin);
            pstmt.setInt(2, pageSize);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Song song = getSong(rs);
                songList.add(song);
            }
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return songList;
    }

    @Override
    public List<Integer> getSongIdList() throws Exception {
        Connection conn = getConnection();
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Integer> ids = new ArrayList<>();
        try {
            String sql = "select id from `song` ";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                //循环获取结果
                Integer id=rs.getInt("id");
                ids.add(id);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            closeResources(rs, pstmt, conn);
        }
        return ids;
    }
















    
    

    private Song getSong(ResultSet rs) throws SQLException {
        Song song = new Song();
        song.setId(rs.getInt("id"));
        song.setUserId(rs.getInt("user_id"));
        song.setColumnId(rs.getInt("column_id"));
        song.setCheckId(rs.getInt("check_id"));
        song.setLikes(rs.getInt("likes"));
        song.setPlayCount(rs.getInt("play_count"));
        song.setStatus(rs.getInt("status"));
        song.setName(rs.getString("name"));
        song.setSongPath(rs.getString("song_path"));
        song.setReason(rs.getString("reason"));
        song.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return song;
    }

    private void setSong(PreparedStatement pstmt, Song song) throws SQLException {
        if (song.getUserId() != null) {
            pstmt.setInt(1, song.getUserId());
        } else {
            pstmt.setNull(1, java.sql.Types.INTEGER);
        }
        if (song.getColumnId() != null) {
            pstmt.setInt(2, song.getColumnId());
        } else {
            pstmt.setNull(2, java.sql.Types.INTEGER);
        }
        if (song.getCheckId() != null) {
            pstmt.setInt(3, song.getCheckId());
        } else {
            pstmt.setNull(3, java.sql.Types.INTEGER);
        }
        if (song.getLikes() != null) {
            pstmt.setInt(4, song.getLikes());
        } else {//默认 0
            pstmt.setInt(4, 0);
        }
        if (song.getPlayCount() != null) {
            pstmt.setInt(5, song.getPlayCount());
        } else {//默认 0
            pstmt.setInt(5, 0);
        }
        if (song.getStatus() != null) {
            pstmt.setInt(6, song.getStatus());
        } else {//默认 0
            pstmt.setInt(6, 0);
        }
        if (song.getName() != null) {
            pstmt.setString(7, song.getName());
        } else {
            pstmt.setNull(7, java.sql.Types.VARCHAR);
        }
        if (song.getSongPath() != null) {
            pstmt.setString(8, song.getSongPath());
        } else {
            pstmt.setNull(8, java.sql.Types.VARCHAR);
        }
        if (song.getReason() != null) {
            pstmt.setString(9, song.getReason());
        } else {
            pstmt.setNull(9, java.sql.Types.VARCHAR);
        }
    }


}
